﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;

namespace Kpmg.Projects.Kdc.Ticket.MyTest
{

    public class Search : KpmgObject
    {
        public string SearchID { get; set; }
        public string Name { get; set; }
        public bool IsPredefined { get; set; }
        public string OwnerStaffNo { get; set; }
        public DateTime CreatedTime { get; set; }
        public string CreatedUser { get; set; }

        public IList<SearchCondition> Conditions { get; private set; }

        public Search()
        {
            this.Conditions = new List<SearchCondition>();
        }
    }

    public enum SearchKey { TicketNo, Requestor, AssignedTo, StartDate, EndDate,Assigned_Staff_No }

    public enum SearchOperator { Equal, NotEqual, Great, GreatOrEqual, Less, LessOrEqual, In }

    public enum SearchDataType { String, Date }

    public class SearchCondition
    {
        public SearchKey Key { get; set; }
        public SearchOperator Operator { get; set; }
        public string Value { get; set; }
        public SearchDataType DataType { get; set; }

        public SearchCondition(SearchKey key, SearchOperator oper, string val, SearchDataType dataType)
        {
            this.Key = key;
            this.Operator = oper;
            this.Value = val;
            this.DataType = dataType;
        }
    }

    public class Ticket
    {
        public string Chargeable_Cost_Centre_No { set; get; }
        public string Requestor_Staff_No { set; get; }
        public string Requestor_Cost_Centre_No { set; get; }
        public string Assigned_Team_Code { set; get; }

    }
    internal static class SearchDAO
    {
        private static AdoTemplate template = AppConfigurer.AdoTemplate;

        private static string strBase = " select search_id, search_name, is_predefined, owner_staff_no, created_time, created_user  "
            + " From search ";

        internal static IList<Ticket> SearchTicket(IList<SearchCondition> lst)
        {

            string strSql = "select Chargeable_Cost_Centre_No,Requestor_Staff_No,Requestor_Cost_Centre_No,Assigned_Team_Code from ticket where ";
            IDbParameters p = template.CreateDbParameters();
            // p.Add("tNo", DbType.String).Value = TicketNo;
            for (int i = 0; i < lst.Count(); i++)
            {
                string key = (lst[i].Key).ToString();
                strSql = strSql + key + "=@key";
                p.Add("key", DbType.String).Value = lst[i].Value;
            }
                     
            IList<Ticket> list = new List<Ticket>();
            Ticket t;
            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader idr)
            {
                t = new Ticket
                {
                    Chargeable_Cost_Centre_No = idr.GetString(0),
                    Requestor_Staff_No = idr.GetString(1),
                    Requestor_Cost_Centre_No = idr.GetString(2),
                    Assigned_Team_Code = idr.GetString(3)
                };
                list.Add(t);

            }, p);
            return list;
        }

        internal static Search GetSearch(string searchID)
        {
            string strSql = strBase + " where search_id = @id ";
            IDbParameters p = template.CreateDbParameters();
            p.Add("id", DbType.String).Value = searchID;

            IList<Search> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql,
                new RowMapperDelegate<Search>(RowMap), p);
            FillConditions(lst);

            return lst.Count == 0 ? null : lst[0];
        }

        internal static IList<Search> ListSearchs(string staffNo)
        {
            string strSql = strBase + " where owner_staff_no = @no ";
            IDbParameters p = template.CreateDbParameters();
            p.Add("no", DbType.String).Value = staffNo;

            IList<Search> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql,
                new RowMapperDelegate<Search>(RowMap), p);
            FillConditions(lst);

            return lst;
        }


        internal static void CreateSearch(Search obj)
        {
            //Get Search_ID
            obj.SearchID = UniqueIDGenerator.Get("S");

            #region Insert into table: search
            string strIns = "insert into search values(@id, @name, @isPredefined, @owner, @cTime, @cUser) ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("id", DbType.String).Value = obj.SearchID;
            p.Add("name", DbType.String).Value = obj.Name;
            p.Add("isPredefined", DbType.String).Value = KpmgObject.BoolToString(obj.IsPredefined);
            p.Add("owner", DbType.String).Value = obj.OwnerStaffNo;
            p.Add("cTime", DbType.DateTime).Value = obj.CreatedTime;
            p.Add("cUser", DbType.String).Value = obj.CreatedUser;

            template.ExecuteNonQuery(CommandType.Text, strIns, p);
            #endregion

            #region Insert into table: search_condition
            strIns = "insert into search_condition values(@id, @sId, @key, @oper, @val, @dType) ";

            foreach (SearchCondition sc in obj.Conditions)
            {
                IDbParameters p1 = template.CreateDbParameters();
                p1.Add("id", DbType.String).Value = UniqueIDGenerator.Get("C");
                p1.Add("sId", DbType.String).Value = obj.SearchID;
                p1.Add("key", DbType.String).Value = sc.Key.ToString();
                p1.Add("oper", DbType.String).Value = sc.Operator.ToString();
                p1.Add("val", DbType.String).Value = sc.Value; ;
                p1.Add("dType", DbType.String).Value = sc.DataType.ToString();

                template.ExecuteNonQuery(CommandType.Text, strIns, p1);
            }
            #endregion
        }

        #region private methods
        private static Search RowMap(IDataReader dr, int rowNumber)
        {
            Search obj = new Search();

            obj.SearchID = dr.GetString(0);
            obj.Name = dr.GetString(1);
            obj.IsPredefined = dr.GetString(2) == "Y" ? true : false;
            obj.OwnerStaffNo = dr.GetString(3);
            obj.CreatedTime = dr.GetDateTime(4);
            obj.CreatedUser = dr.GetString(5);

            return obj;
        }

        private static void FillConditions(IList<Search> lst)
        {
            if (lst == null || lst.Count == 0)
                return;

            IDictionary<string, Search> dic = lst.ToDictionary(x => x.SearchID, y => y);

            string strSql = "select con_id, search_id, con_key, con_operator, con_value, data_type "
                + " from search_condition "
                + " where search_id in (" + DbConvert.ListToInClause(dic.Select(x => x.Key).ToList()) + ") ";

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                string strID = dr.GetString(0);
                string strSearchID = dr.GetString(1);
                string strKey = dr.GetString(2);
                string strOper = dr.GetString(3);
                string strValue = dr.GetString(4);
                string strType = dr.GetString(5);

                SearchKey enumKey = (SearchKey)Enum.Parse(typeof(SearchKey), strKey, true);
                SearchOperator enumOper = (SearchOperator)Enum.Parse(typeof(SearchOperator), strOper, true);
                SearchDataType enumType = (SearchDataType)Enum.Parse(typeof(SearchDataType), strType, true);

                SearchCondition obj = new SearchCondition(enumKey, enumOper, strValue, enumType);

                dic[strSearchID].Conditions.Add(obj);
            });

        }
        #endregion
    }

    public static class SearchOM
    {
        public static Search GetSearch(string searchID)
        {
            if (String.IsNullOrEmpty(searchID))
                return null;

            return SearchDAO.GetSearch(searchID);
        }
        public static IList<Ticket> SearchTicket(IList<SearchCondition> lst)
        {
            if (lst==null)
                return new List<Ticket>();
            return SearchDAO.SearchTicket(lst);
        }
        public static IList<Search> ListSearchs(string staffNo)
        {
            if (String.IsNullOrEmpty(staffNo))
                return new List<Search>();

            return SearchDAO.ListSearchs(staffNo);
        }

        public static void CreateSearch(Search obj, string oper)
        {
            if (obj == null || String.IsNullOrEmpty(oper))
                throw new Exception("SearchOM.CreateSearch::Invalid parameter");

            //Modify data
            obj.CreatedTime = DateTime.Now;
            obj.CreatedUser = oper;

            //Insert into database
            SearchDAO.CreateSearch(obj);
            LogUtil.LogEvent(obj.SearchID, EventAction.CreateSearch, oper, obj.ToString());


        }
    }

}
